package cn.boy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.google.gson.Gson;


import cn.boy.dao.DormitoryDao;
import cn.boy.dao.StudentDao;
import cn.boy.domain.Dormitory;
import cn.boy.domain.Student;
import cn.boy.utils.JdbcUtils;

public class DormitoryDaoImpl implements DormitoryDao {
	
	public void add(Dormitory dorm){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql ="insert into dormitory(" +
					"NO,name,mark,date) values (?,?,?,?)";
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, dorm.getNO());
			ps.setString(2, dorm.getName());
			ps.setInt(3, Integer.parseInt(dorm.getMark()));
			ps.setString(4, dorm.getDate());
			System.out.println(ps);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	
			throw new RuntimeException(e);  
		}finally{	
			JdbcUtils.free(rs, ps, conn);
		}
		
	}
	
	public void delete(String no){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="delete from dormitory where NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			System.out.println(ps);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	/**
	 * 只更新分数
	 * @param dorm
	 */
	public void update(Dormitory dorm){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="update dormitory set mark = ? ,date = ? " +
					" where NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, dorm.getMark());
			ps.setString(2, dorm.getDate());
			ps.setString(3, dorm.getNO());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	public String queryAll(){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Dormitory> list = new ArrayList<Dormitory>();
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from dormitory";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(!rs.next()){
				return "";
			}
			rs.beforeFirst();
			while(rs.next()){
				Dormitory dorm = mappingDormitory(rs);
				list.add(dorm);
			}
			Gson gson = new Gson();
			return gson.toJson(list);
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	
	}
	
	public String query(String no){

		/*Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from dormitory where NO = ? order by date";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if(rs.next()){
				Dormitory dorm = mappingDormitory(rs);
				Gson gson = new Gson();
				return gson.toJson(dorm);
			}
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
		return "";*/
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Dormitory> list = new ArrayList<Dormitory>();
		try {
			conn = JdbcUtils.getConnection();
//			String sql ="select distinct NO, name, mark, date from dormitory where NO like ?";
			String sql ="select * from dormitory where NO = ? order by date desc";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);//____ stand for 4 bit
			rs = ps.executeQuery();
			if(!rs.next()){
				return "";
			}
			rs.beforeFirst();
			while(rs.next()){
				Dormitory dorm = mappingDormitory(rs);
				list.add(dorm);
			}
			Gson gson = new Gson();
			return gson.toJson(list);
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	
	}
	
	public String queryByPrex(String prex){// dormitory no formate ( 02 02 12 )
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Dormitory> list = new ArrayList<Dormitory>();
		try {
			conn = JdbcUtils.getConnection();
//			String sql ="select distinct NO, name, mark, date from dormitory where NO like ?";
			String sql ="select * from dormitory where NO like ? group by NO";
			ps = conn.prepareStatement(sql);
			ps.setString(1, prex + "____");//____ stand for 4 bit
			rs = ps.executeQuery();
			if(!rs.next()){
				return "";
			}
			rs.beforeFirst();
			while(rs.next()){
				Dormitory dorm = mappingDormitory(rs);
				list.add(dorm);
			}
			Gson gson = new Gson();
			return gson.toJson(list);
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	
	}
	
	public String queryByPrexAndDate(String prex, String date) {// dormitory no formate ( 02 02 12 )
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Dormitory> list = new ArrayList<Dormitory>();
		try {
			conn = JdbcUtils.getConnection();
			// only top 10 data
			String sql ="select * from dormitory where NO like ? and date like ? order by mark desc limit 0,10";
			ps = conn.prepareStatement(sql);
			ps.setString(1, prex + "____");//____ stand for 4 bit
			ps.setString(2, "%" + date + "%");
			rs = ps.executeQuery();
			if(!rs.next()){
				return "";
			}
			rs.beforeFirst();
			while(rs.next()){
				Dormitory dorm = mappingDormitory(rs);
				list.add(dorm);
			}
			Gson gson = new Gson();
			return gson.toJson(list);
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	
	}

	private Dormitory mappingDormitory(ResultSet rs) throws SQLException {
		Dormitory dorm = new Dormitory();
		
		dorm.setNO(rs.getString("NO"));
		String name = rs.getString("name");
		if(name!=null && !name.equals("")){
			dorm.setName(name);
		}
		dorm.setMark(rs.getInt("mark") + "");
		dorm.setDate(rs.getString("date"));
		

		return dorm;
	}

	@Override
	public String queryLatest(String no) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from dormitory where NO = ? order by date desc limit 0,1";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if(rs.next()){
				Dormitory dorm = mappingDormitory(rs);
				Gson gson = new Gson();
				return gson.toJson(dorm);
			}
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
		return "";
	}


	

}
